package mall.dao.impl;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.alibaba.druid.pool.DruidDataSource;

/**
 * Jdbc模板类 / 工具类
 * 
 * @author Administrator
 *
 */
public class JdbcTemplate {
	// 声明连接参数
	private final static String driverClassName = "com.mysql.jdbc.Driver";
	private final static String url = "jdbc:mysql://localhost:3306/mall";
	private final static String user = "root";
	private final static String password = "root";

	static {
		// 加载驱动类
		try {
			Class.forName(driverClassName);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}

	// 线程级别的容器，它能够在同一线程内共享一个对象
	private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();

	// 连接池, 也称为"数据源"
	private static DruidDataSource dataSource = new DruidDataSource();

	static {
		dataSource.setDriverClassName(driverClassName);
		dataSource.setUrl(url);
		dataSource.setUsername(user);
		dataSource.setPassword(password);
		dataSource.setMaxActive(10);
		dataSource.setInitialSize(3);
		dataSource.setMaxWait(60000);
	}

	/**
	 * 获取连接对象
	 * 
	 * @return
	 * 
	 */
	public static Connection getConnection() throws SQLException {
		// 先尝方式从threadLocal中获取连接(因为当前线程可能已经调用过了getConnection方法,可能已经存了个连接在threadLocal中了)
		Connection connection = threadLocal.get();
		// 如果threadLocal中空空如也,说明这是当前线程首次调用getConnection()
		if (connection == null || connection.isClosed()) {
			// 这时候就应该创建一个数据库连接对象, 存到threadLocal中, 以供后来者复用
			connection = dataSource.getConnection();
			threadLocal.set(connection);
		}
		return connection;
	}

	public static void closeResult(ResultSet resultSet) {
		if (null != resultSet)
			try {
				resultSet.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}

	public static void closeStatement(Statement statement) {
		if (null != statement)
			try {
				statement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}

	public static void closeConnection(Connection connection) {
		if (null != connection)
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
	}

	public static void closeConnection() {
		try {
			closeConnection(getConnection());
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public static void close(ResultSet resultSet, Statement statement, Connection connection) {
		closeResult(resultSet);
		closeStatement(statement);
		closeConnection(connection);
	}

	public static void close(Statement statement, Connection connection) {
		close(null, statement, connection);
	}

	/**
	 * 执行INSERT / UPDATE / DELETE语句，返回数据库受影响的行数
	 * 
	 * @param sql    SQL语句
	 * @param params SQL语句中的参数
	 * @return
	 * 
	 */
	public static int update(String sql, Object... params) {
		Connection connection = null;
		PreparedStatement statement = null;
		try {
			// 创建连接对象
			connection = JdbcTemplate.getConnection();
			// 创建Statement对象
			statement = connection.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				statement.setObject(i + 1, params[i]); // statement.
			}
			// 发送SQL语句给数据库执行，返回受影响的行数
			return statement.executeUpdate();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			// 释放资源
			closeStatement(statement);
			if (noTransactionRunning()) {
				closeConnection(connection);
			}
		}
	}

	/**
	 * 执行SELECT主句，返回结果集对象ResultSet
	 * 
	 * @param sql
	 * @param params
	 * @return
	 * 
	 */
	public static <T> T query(ResultSetHandler<T> h, String sql, Object... params) {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		try {
			// 创建连接对象
			connection = JdbcTemplate.getConnection();
			// 创建Statement对象
			statement = connection.prepareStatement(sql);
			for (int i = 0; i < params.length; i++) {
				statement.setObject(i + 1, params[i]);
			}
			// 发送SQL语句给数据库执行，返回结果集
			resultSet = statement.executeQuery();
			// ================ 解析结果集 =========================== //
			T result = h.handle(resultSet);
			return result;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			closeResult(resultSet);
			closeStatement(statement);
			if (noTransactionRunning()) {
				closeConnection(connection);
			}
		}
	}

	public static long lastInsertId() {
		String sql = "SELECT LAST_INSERT_ID()";
		return query(new ScalarHandler<BigInteger>(), sql).longValue();
	}

	/**
	 * 开启事务
	 * 
	 * 
	 */
	public static void startTransaction() {
		try {
			Connection connection = JdbcTemplate.getConnection();
			connection.setAutoCommit(false);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 提交事务
	 * 
	 * 
	 */
	public static void commit() {
		try {
			Connection connection = JdbcTemplate.getConnection();
			connection.commit();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	/**
	 * 判断当前连接上是不是没有事务在运行
	 * 
	 * @return
	 */
	private static boolean noTransactionRunning() {
		try {
			return getConnection().getAutoCommit();
		} catch (SQLException e) {
			e.printStackTrace();
			return true;
		}
	}
}
